﻿"""Enhance requirement workflow with attachments, comments, and status values

Revision ID: 20241002_requirement_enhancements
Revises: 20241002_project_templates_members
Create Date: 2025-10-02 14:20:00

"""
# pylint: skip-file
# type: ignore
# pyright: reportAttributeAccessIssue=false, reportGeneralTypeIssues=false
from __future__ import annotations

from typing import Sequence, Union

from alembic import op  # type: ignore[import]
import sqlalchemy as sa


revision: str = "20241002_requirement_enhancements"
down_revision: Union[str, Sequence[str], None] = "20241002_project_templates_members"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


new_status_enum = sa.Enum(
    "draft",
    "clarifying",
    "confirmed",
    "baselined",
    name="requirement_status",
)

old_to_new = {
    "open": "draft",
    "in_progress": "clarifying",
    "completed": "confirmed",
}


def upgrade() -> None:
    bind = op.get_bind()
    dialect = bind.dialect.name

    if dialect == "postgresql":
        op.execute("ALTER TYPE requirement_status RENAME TO requirement_status_old")
        new_status_enum.create(bind, checkfirst=False)
        op.alter_column(
            "requirements",
            "status",
            existing_type=sa.Enum(name="requirement_status_old"),
            type_=new_status_enum,
            existing_nullable=False,
            postgresql_using="status::text::requirement_status",
        )
        op.execute("DROP TYPE requirement_status_old")

    for old, new in old_to_new.items():
        op.execute(
            sa.text("UPDATE requirements SET status = :new WHERE status = :old")
            .bindparams(new=new, old=old)
        )

    op.create_table(
        "attachments",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("requirement_id", sa.Integer(), sa.ForeignKey("requirements.id", ondelete="CASCADE"), nullable=False),
        sa.Column("filename", sa.String(length=255), nullable=False),
        sa.Column("file_path", sa.String(length=512), nullable=False),
        sa.Column("file_type", sa.String(length=64), nullable=False),
        sa.Column("file_size", sa.Integer(), nullable=False),
        sa.Column("uploaded_at", sa.DateTime(timezone=True), server_default=sa.text("(CURRENT_TIMESTAMP)"), nullable=False),
    )
    op.create_index("ix_attachments_requirement_id", "attachments", ["requirement_id"])

    op.create_table(
        "comments",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("requirement_id", sa.Integer(), sa.ForeignKey("requirements.id", ondelete="CASCADE"), nullable=False),
        sa.Column("user_id", sa.Integer(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("content", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("(CURRENT_TIMESTAMP)"), nullable=False),
    )
    op.create_index("ix_comments_requirement_id", "comments", ["requirement_id"])


def downgrade() -> None:
    op.drop_index("ix_comments_requirement_id", table_name="comments")
    op.drop_table("comments")
    op.drop_index("ix_attachments_requirement_id", table_name="attachments")
    op.drop_table("attachments")

    bind = op.get_bind()
    dialect = bind.dialect.name

    if dialect == "postgresql":
        op.execute("CREATE TYPE requirement_status_old AS ENUM ('open','in_progress','completed')")
        op.execute("ALTER TYPE requirement_status_old RENAME TO requirement_status")
        op.execute(
            "UPDATE requirements SET status = CASE status "
            "WHEN 'draft' THEN 'open' "
            "WHEN 'clarifying' THEN 'in_progress' "
            "WHEN 'confirmed' THEN 'completed' "
            "WHEN 'baselined' THEN 'completed' "
            "ELSE status END"
        )
    else:
        op.execute(
            "UPDATE requirements SET status = CASE status "
            "WHEN 'draft' THEN 'open' "
            "WHEN 'clarifying' THEN 'in_progress' "
            "WHEN 'confirmed' THEN 'completed' "
            "WHEN 'baselined' THEN 'completed' "
            "ELSE status END"
        )
